package web.rec;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Formatter;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.jsp.JspWriter;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import com.microsoft.sqlserver.jdbc.SQLServerDriver;

public class ResourceTool {

	private static JSONObject json = jsonReader();
	//private static final String filepath = "C:\\Users\\Alex\\workspace\\CSE132B\\src\\json_forms.txt";
	private static final String filepath = "E:\\workspace\\CSE132B\\src\\json_forms.txt";

	private static Connection conn;  // = getConnection();
	public static String formName;
	private static ArrayList<String> formsList;
	private static Boolean queryComplete = null;
	private static final int COLUMN_MAX = 4;
	
	private static boolean queryError = false;
	
	public static Map<String, String[]> menuMap = initMenuMap();

	
	
	private static JSONObject remappedJSON = null;
	
	public static String[] multi_table = 
		{
			"CoursePrereqs", "Concentration","StudentHoldsDegree",
			"StudentPursuingDegree","FacultySchedule","DegreeCategory","CourseCategory","ConcentrationCourses",
			"SectionWaitingList","StudentTakingSection","StudentQuarterEnrolled",
			"SectionBook","CourseUnits"
		};
	
	public static Map<String, String> tableLookup = initTableLookup();
	
	private static void connect(){
		//String url = "jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b;";
		String url = "jdbc:sqlserver://localhost:1433;database=tritonlink;user=jill;password=3achPunt";
		try {

				DriverManager.registerDriver(new SQLServerDriver());
				//conn = DriverManager.getConnection(url,"alex","12345");
				conn = DriverManager.getConnection(url);
				System.out.println("Connection complete!");


		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static void resetDatabase(){
		
		
		String url = "jdbc:sqlserver://localhost\\SQLEXPRESS"; //;databaseName=cse132b;";
		
		String database = "cse132b";
		String queryPath = "C:\\Users\\Alex\\workspace\\CSE132B\\";
		String fileNameCreateTables = "CSE132B_Create_Tables.sql";
		String fileNameForeignKeys = "CSE132B_ForeignKey_Constraints.sql";
		String fileNameInsertData = "CSE132B_Insert_Data.sql";
		
		
		try {
			conn = null;
			DriverManager.registerDriver(new SQLServerDriver());
			
			conn = DriverManager.getConnection(url,"alex","12345");

			//conn = DriverManager.getConnection(url);
			System.out.println("Connection complete!");
			Statement stmt = conn.createStatement();
			
			try{
				System.out.println("Dropping database");
				stmt.executeUpdate("USE Master; DROP DATABASE " + database + ";");
			}catch(SQLException e){
				System.out.println("Database does not exist!");
				e.printStackTrace();
			}
			
			System.out.println();
			try{
				System.out.println("Creating database");
				stmt.executeUpdate("USE Master; CREATE DATABASE " + database + ";");
			}catch(SQLException e){
				System.out.println("Database already exists!");
				e.printStackTrace();
			}
			
			
			stmt.executeUpdate("USE " + database + ";");
			BufferedReader br = new BufferedReader(
					new FileReader(queryPath + fileNameCreateTables));

			String line;
			while((line = br.readLine()) != null ){
				if(line.contains("--") || line.trim().equals("")){
					continue;
				}
				
				stmt.addBatch(line);
				
			}
			
			int [] results =  stmt.executeBatch();
			
			
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	
	public static Connection getConnection(){
		if(conn == null)
			connect();
		return conn;
	}
	
	public static void setForm(String form){
		formName = form;
		queryComplete = null;
	}
	
	public static String getForm(){
		return formName;
	}
	
	public static void init(){
		json = jsonReader();
		conn = getConnection();
		tableLookup = initTableLookup();

		menuMap = initMenuMap();
	}
		
	public static ArrayList<String> getFormList(){
		if(formsList == null)
			init();
		
		return formsList;
		
		
	}
	
	public static Map<String, String[]> initMenuMap(){
		if(menuMap!= null)
			return menuMap;
		
		String [] degree_list = {"Degree", "Concentration","Category", "Department"};
		String [] course_list = {"Course", "Class","Section", "Meeting", "SectionWaitingList"};
		String [] student_list = {"Ungrad","SpecialMS", "MSStudent", 
				"PHDPreCand", "PHDCandidate","GradCommittee"};
		String [] faculty_list = {"Faculty","FacultySchedule"};
		
		menuMap = new HashMap<String,String[]>();
		menuMap.put("Degree data", degree_list);
		menuMap.put("Student data", student_list);
		menuMap.put("Course data", course_list);
		menuMap.put("Faculty data", faculty_list);
		
		return menuMap;
		
	}

	public static String getMenu(String menuName){
		StringBuilder menu = new StringBuilder(500);
		String[] menuArr = menuMap.get(menuName);
		
		for(String s: menuArr){
			menu.append("<a href=\"formspage.jsp?form=" + s +"\">" + s +"</a><br />");
		}
		if(menuName.equals("Student data")){
			menu.append("<a href=\"StudentEnrollment.jsp\">StudentEnrollment</a><br />");
			menu.append("<a href=\"ClassesTaken.jsp\">ClassesTaken</a><br />");
		}
		
		//
		return menu.toString();
	}
	
	public static String getErrorString(){
		if(queryError == false){
			return "";
		}
		return "query error";
	}
	
	/*
	 * Initializes a mapping from the form name to the table that will be queried
	 */
	private static Map<String, String> initTableLookup(){
		if(tableLookup != null)
			return tableLookup;
		
		Map<String,String> tableMap = new HashMap<String,String>();
				
		tableMap.put("Course", 
				"Course,CoursePrereqs,CourseCategory,CourseUnits,ConcentrationCourses");
		tableMap.put("Class","Class");
		tableMap.put("ClassMeeting", "Section,Meeting,FacultySchedule");
		tableMap.put("StudentPursuingDegree","StudentPursuingDegree");
		tableMap.put("Ungrad",
				"Student,Ungrad");
		tableMap.put("MSStudent",
				"Student,Grad,MSStudent");
		
		tableMap.put("PHDPreCand",
				"Student,Grad,PHDStudent,PHDPreCand");
		tableMap.put("PHDCandidate",
				"Student,Grad,PHDStudent,PHDCandidate");
		tableMap.put("GradCommittee", "GradCommittee");
		
		tableMap.put("SpecialMS",
				"Student,Ungrad,SpecialMS");
		tableMap.put("Faculty","Faculty");
		
		tableMap.put("Category", "Category");
		tableMap.put("Concentration", "Concentration");
		tableMap.put("Degree", "Degree,DegreeCategory");
		tableMap.put("Department", "Department");
		tableMap.put("Section", "Section,SectionBook");
		
		tableMap.put("ClassesTaken", "Class,StudentTakingSection");
		tableMap.put("CourseEnrollment", "StudentQuarterEnrolled");
		
		return tableMap;
		
	}
	
	/*
	 * Reads the json_forms.txt file and initializes a JSONObject
	 * to specify what fields will be prompted for input
	 */
	private static JSONObject jsonReader(){
		if(json != null)
			return json;
		
		StringBuffer sb = new StringBuffer(500);
		try{
			
			BufferedReader br = new BufferedReader(new FileReader(filepath));
			
			String line;
			while((line = br.readLine())!= null){
				Pattern p = Pattern.compile("\\s*");
				Matcher m = p.matcher(line);
				line = m.replaceFirst("");
				sb.append(line);
			}
			JSONObject jtmp = new JSONObject(sb.toString());
			Iterator it = jtmp.keys();
			formsList  = new ArrayList<String>();
			while(it.hasNext()){
				String val = (String) it.next();
				formsList.add(val);
			}
			return jtmp;
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}	
	/*
	 * Attempts to insert data into the database
	 * - if the insertion fails then the variable queryComplete is set to false
	 * 	 and a message will be displayed in the table header
	 */
	public static void insertDB(HttpServletRequest request) throws SQLException{

		try {
			
			// If there is no JSON form defined then only load a single form
			JSONArray jarr = jsonReader().getJSONArray(formName);
			Map<String, String> map = initTableLookup();
			String tableList = map.get(formName);
		
			String [] tables = null;
			if(tableList != null){
				tables = tableList.split(",");
			}
			else if(tableList == null || jarr == null){
				tables = new String [1];
				tables[0] = formName;
			}
			Set<String> multiValued = new HashSet<String>(Arrays.asList(multi_table));
			for(String tableName: tables)
			{
				if(multiValued.contains(tableName)&&!tableName.equals(formName)){
					continue;
				}
				insertDBForEachTable(request,tableName);
			}
		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public static void insertDBForEachTable(HttpServletRequest request,String tableName) throws SQLException{
		
		try{
			Statement stmt = conn.createStatement();
			
			Map<String,String[]> parameterMap = request.getParameterMap();
			
			//String qry = "SELECT insert_query FROM TableQueries t WHERE t.table_name ='"+ tableName +"'";
			//ResultSet prepSet = stmt.executeQuery(qry);
			//prepSet.next();
			//String prepstr = prepSet.getString(1);
			
			//PreparedStatement ps = conn.prepareStatement(prepstr);
			
			ResultSet cset = stmt.executeQuery("SELECT * FROM " + tableName);
			ResultSetMetaData tableMetaData = cset.getMetaData();
			
			String colType,val;
			String colName;
			String [] paramValueArray = null;
			int indexOfPrep = 1;
			
			StringBuilder columnNames = new StringBuilder(); 
			StringBuilder valueList = new StringBuilder(); 
			boolean first = true;
			
			for(int tableColIndex = 1; tableColIndex <= tableMetaData.getColumnCount() ;tableColIndex++){
				colName = tableMetaData.getColumnName( tableColIndex );
				colType = tableMetaData.getColumnTypeName( tableColIndex );
				
				// parameters from the URL parameter map
				paramValueArray = parameterMap.get(colName);
				
				// ignore if the parameter value is null
				if(paramValueArray == null)	
					continue;
				
				String value = paramValueArray[0];
				if(value==null || value.equals(""))
					continue;
				
				
				if(!first){
					columnNames.append(",");
					valueList.append(",");
				}else{
					first = false;
				}
				
				
					
				
				// Check the type of data
				if(colType.equals("varchar")||colType.equals("datetime")){
					value = "'" + value + "'";
				}else if(colType.equals("int")){
					if(value.toLowerCase().charAt(0) == 'y'){
						value = "1";
					}
					else if(value.toLowerCase().charAt(0) == 'n'){
						value = "0";
					}
					
				}
				
				columnNames.append(colName);
				valueList.append( value );
				/*
				for(int indexParamArray = 0 ; indexParamArray < paramValueArray.length ;indexParamArray++)
				{
					if( indexParamArray > 0 )
					{
						ps.executeUpdate();
					}
					val = paramValueArray[indexParamArray];
					
					if( colType.equals("varchar") )
						ps.setString(indexOfPrep, val);
					else if( colType.equals("int") ){
						if(val.charAt(0) == 'y' || val.charAt(0)=='Y'){
							val = "1";
						}else if(val.charAt(0) == 'n' || val.charAt(0)=='N'){
							val = "0";
						}
						ps.setInt(indexOfPrep, Integer.valueOf(val) );
					}
					else if(colType.equals("float")){
						ps.setFloat(indexOfPrep, Float.valueOf(val));
					}
					indexOfPrep++;
				}
				*/
			}
			String qry = "INSERT INTO " + tableName + " (" + columnNames + ") VALUES (" +
					valueList + ")";
			System.out.println(qry);
			stmt.executeUpdate(qry);
			//ps.executeUpdate();
			queryComplete =  true;
		}catch(SQLException e){
			e.printStackTrace();
			queryComplete =  false;
		}
	}

	
	
	/*
	 * Attempts to modify the data by either "update" or "delete" queries
	 * by the action passed in
	 */
	public static void modifyDB(HttpServletRequest request, String action){
		Map<String, String> map = initTableLookup();
		String tableList = map.get(formName);
		String [] tables;
		if(tableList == null){
			tables = new String[1];
			tables[0] = formName;
		}
		else{
			tables = tableList.split(",");
		}
		
		
		for(String tableName: tables)
		{
			modifyDBForEachTable(request,action,tableName);
		}
	}
	public static void modifyDBForEachTable(HttpServletRequest request, String action, String tableName){
		try{
		    ResultSet rs = null;
		    StringBuilder qry = new StringBuilder(200);
		    Formatter qryFmt = new Formatter(qry);
		    Statement stmt = conn.createStatement();
		    boolean update = action.equals("update");
		    
		    if(!update && !action.equals("delete")){
		    	throw new SQLException("Invalid database action");
		    }
		    	
		    StringBuilder setQry = new StringBuilder(200);
		    Formatter setFmt = new Formatter(setQry);
		    
		    StringBuilder keyQry = new StringBuilder(200);
		    Formatter keyFmt = new Formatter(keyQry);
		    
		    
		    DatabaseMetaData meta = conn.getMetaData();

		    Map<String,String[]> map = request.getParameterMap();
		    
		    rs = meta.getPrimaryKeys(null, null, tableName);
	
		    ArrayList<String> pkeys = new ArrayList<String>();
		    while (rs.next()) {
		    	String columnName = rs.getString("COLUMN_NAME");
		    	pkeys.add(columnName);

		    }
			rs = meta.getColumns(null, null, tableName, null);
			boolean firstKey = true;
			boolean firstSet = true;
			//input value of a given column name
			String [] valueArray;
			
			while (rs.next()) {
				String columnName = rs.getString("COLUMN_NAME");
		    	String type = rs.getString("TYPE_NAME");
		    	valueArray = map.get(columnName);
		    	if(valueArray == null)
		    		continue;
		    	String value = valueArray[0];    	    	
		    	if(pkeys.contains(columnName)){
			    	if(!firstKey){
			    		keyQry.append(" and ");
			    		}else{
			    		firstKey = false;
			    		}
		    		if(type.equals("varchar")||type.equals("datetime"))
		    			keyFmt.format("%s='%s' ", columnName, value);
		          	else
		          	{
		          		if(value.toLowerCase().charAt(0) == 'y'){
							value = "1";
						}
						else if(value.toLowerCase().charAt(0) == 'n'){
							value = "0";
						}
		          		keyFmt.format("%s=%s ", columnName, value);
		          	}
		    	}else{
		    		if(value == null || value.equals("")){
		    			continue;
		    		}
			    	if(!firstSet){
			    		setQry.append(",");
			    	}else{
			    		firstSet = false;
			    	}    		
			    	if(type.equals("varchar")||type.equals("datetime"))
			      		setFmt.format(" %s='%s'", columnName, value);
			      	else
			      	{
		          		if(value.toLowerCase().charAt(0) == 'y'){
							value = "1";
						}
						else if(value.toLowerCase().charAt(0) == 'n'){
							value = "0";
						}
			      		setFmt.format(" %s=%s ", columnName, value);
			      	}
		    	}
			      
			}
		    
			if(update){
				qryFmt.format("UPDATE %s SET %s WHERE %s", tableName, setFmt, keyFmt );
			}
			else{
				qryFmt.format("DELETE FROM %s WHERE %s", tableName, keyFmt );
			}
			System.out.println(qryFmt.toString());
			stmt.executeUpdate( qryFmt.toString() );
		    
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	
	
	public static void buildInput(HttpServletRequest request, JspWriter out){
		Map<String, String> map = initTableLookup();
		String tableList = map.get(formName);
		String [] tables;
		if(tableList != null)
			tables = tableList.split(",");
		else{
			tables = new String [1];
			tables[0] = formName;
		}
		
		//int COLUMN_MAX = 7;
		Set<String> columnsAdded = new HashSet<String>();
		Set<String> multiValued = new HashSet<String>(Arrays.asList(multi_table));
		
		DBTableTool dbTool = new DBTableTool(conn, tables[0]);		
		DBTableData mainTableData = dbTool.getTableData(tables[0]);
		
		ArrayList<String> header = new ArrayList<String>();
		ArrayList<String> row = new ArrayList<String>();
		
		remappedJSON = remapJSON(new ArrayList<String>( Arrays.asList(tables)) , request, dbTool);
		if(mainTableData.getColumnList().isEmpty()){
			Iterator iter = remappedJSON.keys();
			ArrayList<String> columns = new ArrayList<String>();
			while(iter.hasNext()){
				String val = iter.next().toString();
				columns.add(val);
			}
			addColumnsToDiv(columns,row,header,columnsAdded,remappedJSON);
		}
		else{
		
			//ArrayList<ArrayList<String>> htmlTable = new ArrayList<ArrayList<String>>();
			List<String> pkeys = mainTableData.getPkeyList();
			List<String> newpkeys = new ArrayList<String>();
			Iterator<String> itor = pkeys.iterator();
			while(itor.hasNext()){
				String tmp = itor.next();
				itor.remove();
				newpkeys.add(tmp.split(" ")[0]);
			}
			
			addColumnsToDiv(newpkeys,row,header,columnsAdded, remappedJSON);
			addColumnsToDiv(mainTableData.getColumnList(),row,header,columnsAdded, remappedJSON);
			
			//addColumnsToHtmlList(newpkeys,columnsAdded,htmlTable, remappedJSON);
			//addColumnsToHtmlList(mainTableData.getColumnList(),columnsAdded,htmlTable, remappedJSON);
			
			
			for(int i = 1 ; i < tables.length ; i++ ){
				/*if(row.size() >= COLUMN_MAX){
					htmlTable.add(header);
					htmlTable.add(row);
					
					header = new ArrayList<String>();
					row = new ArrayList<String>();
				}
				*/
				if(multiValued.contains(tables[i])){
					header.add(null);
					row.add("<a href=\"javascript:poponclick(\'" + tables[i] + "\')\">" + tables[i] +"</a>");
					continue;
				}
				
				List<String> columnSet = dbTool.getTableData(tables[i]).getColumnList();
				//addColumnsToHtmlList(columnSet,columnsAdded,htmlTable, remappedJSON);
				addColumnsToDiv(columnSet,row,header,columnsAdded, remappedJSON);
				
			}// end of outer for loop
			
		}
		
		try{
			for(int i = 0; i < row.size() ; i++){
				String headStr = header.get(i);
				String rowStr = row.get(i);
				if(headStr == null)
					headStr = "";
				if(rowStr == null){
					rowStr = "";
				}
				
				out.append("<div>");
				out.append("<p>" + headStr + "</p><br />");
				out.append(rowStr);
				out.append("</div>");
			}
			
			
		}catch(Exception e){
			e.printStackTrace();
		}
		
		//System.out.println(htmlTable);
	/*	
		int rowind = 0;
		// add column for buttons
		for(ArrayList<String> arr : htmlTable){
			if(rowind == 1){
				arr.add("<input type=\"submit\" value=\"insert\" />" +
						"<button type='button' " +
						"onclick='window.open(updateFields(\"" + formName 
						+"\"),\"_self\");return false;' />update</button>");
			}
			arr.add(null);
			rowind++;
		}
		
		
		
		try {
			out.append( formatArrayListAsHtmlTable(htmlTable) );
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		*/
	}
	
	
	public static void addColumnsToDiv(List<String> columnSet, List<String> row, 
			List<String> header, Set<String> columnsAdded, JSONObject jmap)
	{
		for(String col: columnSet){
			if(columnsAdded.contains(col))
				continue;
			

			header.add(col.replace("_", " "));
			row.add( setHtmlInput( col, jmap) );
			
			columnsAdded.add( col );

		}
	}
	
	/*
	 * 
	 */
	public static void addColumnsToHtmlList(List<String> columnSet , Set<String> columnsAdded, 
			ArrayList<ArrayList<String>> htmlTable, JSONObject jmap){
		
		ArrayList<String> row = null;
		ArrayList<String> header = null;
		if(!htmlTable.isEmpty()){
			header = htmlTable.get(htmlTable.size()-2);
			row = htmlTable.get(htmlTable.size()-1);
			
		}


		if(row == null || row.size() == COLUMN_MAX){
			header = new ArrayList<String>();
			row = new ArrayList<String>();
			htmlTable.add(header);
			htmlTable.add(row);
		}
		
		
		for(String col: columnSet){
			if(columnsAdded.contains(col))
				continue;
			
			if(row.size() >= COLUMN_MAX){
				
				header = new ArrayList<String>();
				row = new ArrayList<String>();
				htmlTable.add(header);
				htmlTable.add(row);
				
			}

			header.add(col.replace("_", " "));
			row.add( setHtmlInput( col, jmap) );
			
			columnsAdded.add( col );

		}
		
		
	}
	
	
	/*
	 * Re-maps the JSON Object in as "objName":{ type:text , table:tblName , ...}
	 * so the elements can be referenced by name. 
	 * 
	 */
	public static JSONObject remapJSON(ArrayList<String> tableSet, HttpServletRequest request, DBTableTool dbTool){
		JSONObject json_obj = new JSONObject();
		JSONArray jarr = null;
		
		JSONObject copy = null;
		try {
			copy = new JSONObject(json.toString());
		} catch (JSONException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		
		Map<String,String[]> map = request.getParameterMap();
		for(String table: tableSet){
			try {
				jarr = copy.getJSONArray(table);
				for(int i = 0; i < jarr.length() ; i++){
					JSONObject tmp = jarr.getJSONObject(i);
					String name = tmp.getString("name");
					
					if(json_obj.has(name))
						continue;
					String[] valueArray = map.get(name);
					String value;
					
					if(valueArray == null){
						value = "";
					}else{
						value = valueArray[0];
					}
					
					if(!tmp.has("value"))
						tmp = tmp.put("value", value);
					
					Object o = tmp.remove("name");
					json_obj.put(name, tmp);
				}
				
				
				
			} catch (JSONException e) {
				e.printStackTrace();
				List<String> set = dbTool.getTableData(table).getColumnList();
				for(String s: set){
					if(json_obj.has(s))
						continue;
					JSONObject value;
					try {
					
						String[] valueArray = map.get(s);
						String val;
						
						if(valueArray == null){
							val = "";
						}else{
							val = valueArray[0];
						}
						value = new JSONObject("{\"type\"=\"text\",\"value\"=\""+val +"\"}");
						json_obj.put(s, value);
						
					} catch (JSONException e1) {
						// TODO Auto-generated catch block
						e1.printStackTrace();
					}
				}
			}
		}
		return json_obj;
	}
	
	
	/*
	 * This function sets the html input fields such as <input type="text" ... />
	 * using the JSON object
	 */
	public static String setHtmlInput(String colName, JSONObject jobj){
		StringBuilder sb = new StringBuilder();
		Formatter fmt = new Formatter(sb);
		
		try{

			
			JSONObject colObject = jobj.getJSONObject(colName);
			String type = colObject.getString("type");
			String val = colObject.getString("value");
			
			if(type.equals("select")){
				String length = "";
				String queryTables = "";
				String target = "";
				try{
					length = colObject.getString("multi");
					queryTables = colObject.getString("query");
					target = colObject.getString("target");
				}catch(Exception e){
					System.err.println("multi, query, or target is not defined in setHtmlInput()");
					
				}
				
				
				fmt.format("<select name=\"%s\" class=\"field\" >", colName);
				String table = colObject.getString("table");
				//if this field has a different name as primary key
				if(colObject.has("fk"))
				{
					colName = colObject.getString("fk");
				}
				Statement stmt = conn.createStatement();
				ResultSet rs = stmt.executeQuery("SELECT " + colName + " FROM " + table);
				while(rs.next()){
					String optionVal = rs.getString(1);
					if(val.equals(optionVal)){
						fmt.format("<option selected=\'selected\'>%s</option>", rs.getString(1));
					}else{
						fmt.format("<option>%s</option>", rs.getString(1));
					}
					
				}
				sb.append("</select>");
				
				// script function query(tables , pkey,targetName)
				if(!queryTables.equals("")){
					String searchButton = "<br />" +
							"<button value='search' onclick=\"query('"
							+ queryTables + "','" + colName + "','" + target 
							+ "');return false;\" size=\"" + length + "\" >search</button>";
					sb.append(searchButton);
				}
				
			}else{
				fmt.format("<input class=\"field\" type=\"%s\" value=\"%s\" name=\"%s\" />", type, val, colName);
			}
			return sb.toString();
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	/*
	 * Builds the proper form given the form value initialized when the page loads
	 */
	public static void buildInputForm(HttpServletRequest request, JspWriter out){
		
		Map<String, String> map = initTableLookup();
		String tableList = map.get(formName);
		String [] tables;
		if(tableList != null)
			tables = tableList.split(",");
		else{
			tables = new String [1];
			tables[0] = formName;
		}
		
		//Map<String,String>
		
		try {
		
			Set<String> fieldList = new HashSet<String>();
			StringBuilder header;
			StringBuilder input;
			
			boolean first = true;
			
			for(String tableName : tables)
			{
				header = new StringBuilder(200);
				input = new StringBuilder(200);
				
				header.append("<tr>");
				input.append("<tr>");
				
				buildInputFormForEachTable(request,header,input,tableName,fieldList);
				if(first){
					String failedQuery = "<td></td>";
					if(queryComplete != null && !queryComplete){
						failedQuery = "<td id='errdata'>query failed!</td>";
					}
					header.append(failedQuery);
					first = false;
				}
				
				out.append(header.toString() + "</tr>");
				out.append(input.toString() + "</tr>");

			}

			
	        String button = "<button type='button' onclick='window.open(updateFields(\""+ formName +
	        		"\"),\"_self\");return false;' />update</button>";
	        out.append("<td><input type='submit' value='insert' /> " +
	                        button + "</td></tr>");
	        
	

     
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static String formatArrayListAsHtmlTable(ArrayList<ArrayList<String>> htmlTable){
		StringBuilder sb = new StringBuilder();
		Formatter fmt = new Formatter(sb);
		
		for(ArrayList<String> rowList : htmlTable){
			sb.append("<tr>");
			for(String cols : rowList ){
				if(cols == null){
					cols = "";
				}
				fmt.format("<td>%s</td>", cols);
			}
			sb.append("</tr>\n");
		}
		return sb.toString();
	}
	
/*
	public static Set<String> getColumnData(String table){
		Set<String> columnSet = new HashSet<String>();
		String [] tables;
		
		String tableList = initTableLookup().get(table);
		if(tableList != null)
			tables = tableList.split(",");
		else{
			tables = new String [1];
			tables[0] = table;
		}
		
		
		try {
			Statement stmt = conn.createStatement();
			ResultSet rs = null;
			ResultSetMetaData rsmd = null;
			for(String tableName: tables){
				rs = stmt.executeQuery("SELECT * FROM " + tableName );
				rsmd = rs.getMetaData();
				
				
				
				for(int i = 1; i <= rsmd.getColumnCount(); i++){
					String name = rsmd.getCatalogName(i);
					columnSet.add(e)
				}
				
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	*/
	
	public static boolean isMultiValued(String table){
		for(String s: multi_table){
			if(s.equals(table))
				return true;
		}
		return false;
	}
	
	

	/*
	 * Builds the insert part for each table related to this entry page
	 */
	public static void buildInputFormForEachTable(HttpServletRequest request,
			StringBuilder header, StringBuilder input,String tableName,Set<String> fieldSet){
		
		Map<String, String[]> parameterMap = request.getParameterMap();
		String [] value;
		
		
		if(isMultiValued(tableName) && !parameterMap.containsKey("popup") ){
			String script = "javascript: poponclick('" + tableName + "');";
			header.append("<td></td>");
			input.append("<td><a href=\"" + script + "\">" + tableName + "</a></td>");
			return;
		}
		
		

		JSONObject field;
		Formatter headfmt = new Formatter(header);
		Formatter inpfmt = new Formatter(input);

		String type,name, table, primaryKey;
		type = name = table = primaryKey = null;
		
		
		try 
		{
			try 
			{
				JSONArray json_arr = json.getJSONArray(tableName);
				
				for(int i = 0; json_arr!= null && i < json_arr.length() ; i++ ){
					field = json_arr.getJSONObject(i);
					
					name = field.getString("name");
					type = field.getString("type");
					if(fieldSet.contains(name))
					{
						continue;
					}
					else
					{
						fieldSet.add(name);
					}
					value = parameterMap.get(name);
					
					headfmt.format("<td>%s</td>", name.replace("_", " ") );
					
					if(!type.equals("select")){
						inpfmt.format("<td><input class='field' type='%s' name='%s' ", type,name);
						if(value != null ){
							input.append("value='" + value[0] + "'");
						}
						input.append(" /></td>\n");
						
					}
					else
					{
						inpfmt.format("<td><select class='field' name='%s'>\n", name);
						table = field.getString("table");
						if(field.has("fk"))
						{
							primaryKey = field.getString("fk");
						}
						else
						{
							primaryKey = name;
						}
						Statement stmt = conn.createStatement();
						ResultSet rs = stmt.executeQuery("SELECT DISTINCT " + primaryKey + " FROM " + table);
						
						String s;
						
						while(rs.next()){
							String val = rs.getString(1);
							if(value != null && value[0].equals(val)){
								s = "selected='selected'";
							}
							else
								s = "";
							
							inpfmt.format("\t<option %s >%s</option>\n", s, val);
							
						}
						input.append("</select></td>\n");
						
					}
						
				} // end for loop
				
			// If there is no JSONObject specified for the form input then build the table 
			// from the default column names and inputs
			} catch (JSONException je){	
				
				Statement stmt = getConnection().createStatement();
				ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);
				ResultSetMetaData rsmd = rs.getMetaData();
				String colName;
				
				for(int i = 1; i <= rsmd.getColumnCount() ; i++ ){
					colName = rsmd.getColumnName( i );
					String [] tmpArr = parameterMap.get(colName);
					if(fieldSet.contains(colName))
						continue;
					
					String val;
					if(tmpArr == null){
						val = "";
					}
					else{
						val = tmpArr[0];
					}
					
					headfmt.format("<td>%s</td>", colName.replace("_", " ") );
					inpfmt.format("<td><input class='field' type='%s' name='%s' value='%s' /></td>", type,colName,val);
				}
				
				
				
			} // end of inner try block
		
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		
	}
	
	/*
	 * Places the data that is currently in the database in a table and formats it
	 */
	public static void buildOutputRows(JspWriter out, String table){
		
		Map<String, String> map = initTableLookup();
		String [] tables;
		
		String tableList = map.get(table);
		if(tableList != null)
			tables = tableList.split(",");
		else{
			tables = new String [1];
			tables[0] = table;
		}
		
		Set<String> fieldList = new HashSet<String>();
		Set<String> fieldList2 = new HashSet<String>();
		int row = 0;
		try{
			String queryString = "SELECT * FROM ";
			String lastTable = null;
			JSONArray jarr = null;
			JSONObject field = null;
			//for the table field in the json file
			String referedTable;
			//for each  primary key foreign key pairs
			Map<String,String> fkPkPairs = new HashMap<String,String>();
			Set<String> multiValued = new HashSet<String>(Arrays.asList(multi_table));
			for(String tableName : tables)
			{			
				if(multiValued.contains(tableName)&&!tableName.equals(formName)){
					continue;
				}
				jarr = json.getJSONArray(tableName);
				if(lastTable == null)
				{
					queryString += tableName + " ";
					for(int i=0;i<jarr.length();i++)
					{
						field = jarr.getJSONObject(i);
						//if this is a foreign key from the first table referring the second table
						fieldList.add(field.getString("name"));
					}
				}
				else
				{
					queryString += " INNER JOIN "+ tableName;
					for(int i=0;i<jarr.length();i++)
					{
						field = jarr.getJSONObject(i);
						fieldList.add(field.getString("name"));
						//if this is a foreign key from the first table referring the second table
						if(field.has("table"))
						{
							referedTable = field.getString("table");
							if(referedTable.equals(lastTable))
							{
								if(field.has("fk"))
								{
									fkPkPairs.put(field.getString("fk"),field.getString("name"));
								}
								else
								{
									fkPkPairs.put(field.getString("name"),field.getString("name"));
								}
							}
						}
					}
					boolean first = true;
					for(String pkey: fkPkPairs.keySet())
					{
						String fkey = fkPkPairs.get(pkey);
						if(first)
						{
							queryString += " ON ";
							first = false;
						}
						else
						{
							queryString += " AND ";
						}
						queryString += lastTable + "." + pkey + " = " + tableName + "." + fkey;
					}
				}
				lastTable = tableName;
			}
			System.out.println(queryString);
			Statement stmt = conn.createStatement();
			
			ResultSet rs = stmt.executeQuery(queryString);
			ResultSetMetaData rsMetaData = rs.getMetaData();
			int count = rsMetaData.getColumnCount();
			String name = null;
			while(rs.next()){
				//out.append("<tr class='output'>");
				out.append("<div class=\"output\">");
				for(int i=1;i<=count;i++)
				{
					name = rsMetaData.getColumnLabel(i);
					boolean list1 = fieldList.contains(name);
					boolean list2 = fieldList2.contains(name);
					
					if( list1 && !list2 )
					{
						out.append("<div><b>" + name.replace("_", " ") + ":</b> <div>" + rs.getString(name) + "</div></div>");
						
						//out.append("<td>" + rs.getString(name) + "</td>");
						fieldList2.add(name);
					}
				}
				out.append("<div class=\"modButtons\"><button type='button' onclick='window.open(trUrlData(" + row + 
						",\"modify\",\"" + formName +"\"),\"_self\");return false;' />modify</button>" +
						"<button type='button' onclick='window.open(trUrlData(" + row + 
						",\"delete\",\"" + formName +"\"),\"_self\");return false;' />delete</button></div>");
				out.append("</div>");
				
				
				/*out.append("<td><button type='button' onclick='window.open(trUrlData(" + row + 
						",\"modify\",\"" + formName +"\"),\"_self\");return false;' />modify</button>" +
						"<button type='button' onclick='window.open(trUrlData(" + row + 
						",\"delete\",\"" + formName +"\"),\"_self\");return false;' />delete</button></td>");
				*/
				
				row++;
				out.append("</tr>");
				fieldList2.clear();
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	/*
	 * Places the data that is currently in the database in a table and formats it
	 */
	public static void buildOutputRows(JspWriter out){
		buildOutputRows(out,formName);
	}
	
	public static void buildOutputFromDatabase(){
		DBTableTool dbTool = new DBTableTool(conn, formName);
		
	}
	public static int gradeToIndex(String grade) {
		if ("4.3".equals(grade)) {
		return 0;
		} else if ("4".equals(grade) || "4.0".equals(grade)) {
		return 0;
		} else if ("3.7".equals(grade)) {
		return 0;
		} else if ("3.4".equals(grade)) {
		return 1;
		} else if ("3.1".equals(grade)) {
		return 1;
		} else if ("2.8".equals(grade)) {
		return 1;
		} else if ("2.5".equals(grade)) {
		return 2;
		} else if ("2.2".equals(grade)) {
		return 2;
		} else if ("1.9".equals(grade)) {
		return 2;
		} else if ("1.6".equals(grade)) {
		return 3;
		} else {
		return 4;
		}
	}
	
	//private static String outputQuery(String table)
	
	
	public static void main(String [] args)
	{
		
		//ResourceTool.resetDatabase();
		
		/*try
		{
		ResultSet rs = null;
		Connection conn = ResourceTool.getConnection();
	    DatabaseMetaData meta = conn.getMetaData();
	     // The Oracle database stores its table names as Upper-Case,
	     // if you pass a table name in lowercase characters, it will not work.
	     // MySQL database does not care if table name is uppercase/lowercase.
	     //
	     rs = meta.getExportedKeys(conn.getCatalog(), null, "Student");
	     while (rs.next()) {
	       String fkTableName = rs.getString("FKTABLE_NAME");
	       String fkColumnName = rs.getString("FKCOLUMN_NAME");
	       int fkSequence = rs.getInt("KEY_SEQ");
	       System.out.println("getExportedKeys(): fkTableName="+fkTableName);
	       System.out.println("getExportedKeys(): fkColumnName="+fkColumnName);
	       System.out.println("getExportedKeys(): fkSequence="+fkSequence);
	     }
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		*/
	}
}
